#放数据库有关的文件
from unittest import result

import pymysql
#定义和数据库的连接
conn = pymysql.connect(
    host='rm-2ze7flpq72t9m0c83fo.mysql.rds.aliyuncs.com',
    port=3306,
    user='root',
    passwd='Root1234',
    db='assistant',
    charset='utf8',
    cursorclass=pymysql.cursors.DictCursor
)
#根据用户名查询用户是否存在的函数
def query_user_no_exits(username):
    sql = 'select * from sys_user where username = %s'
    cur = conn.cursor()
    cur.execute(sql, [username])
    result = cur.fetchone()
    if result is None:
        return True
    else:
        return False

def query_login_no_exits(username):
    sql = 'select * from sys_user where username = %s'
    cur = conn.cursor()
    cur.execute(sql, [username])
    result = cur.fetchone()
    if result is None:
        return False
    else:
        return True

def query_password_exits(username):
    sql = 'select * from sys_user where username = %s'
    cur = conn.cursor()
    cur.execute(sql, [username])
    #result是从数据库查询回来的用户信息
    #result有两种结果1.空
    result = cur.fetchone()
    return result


#根据用户名和密码添加数据的函数
def add_user(username, password):
    sql = 'insert into sys_user (username, password) values (%s, %s)'
    cur = conn.cursor()
    cur.execute(sql, [username, password])
    conn.commit()

# 3、根据用户id查询当前用户的AI助手聊天信息的函数
def query_message_by_user_id(user_id):
    sql = 'select * from chat_message where user_id = %s order by message_time asc'
    cur = conn.cursor()
    cur.execute(sql, [user_id])
    list = cur.fetchall()
    return list